// excel-helper.ts
// 环境：node + typescript + exceljs
// 只导出一个 excel(...) 函数，返回 { workbook, saveRows, getRows, deleteRows }

import crypto from 'crypto';
import fs from "fs";
import path from "path";
import ExcelJS, { Workbook, Worksheet, DataValidation as ExcelDataValidation } from "exceljs";
import { log } from "console";
import * as r from "radash"

export interface PATTR {
  canInput?: boolean;
  multiple?: boolean;
  required?: boolean;
  min?: number;
  max?: number;
  unit?: string;
  options?: string[];
  delWords?: string[];
  type?: "select" | "text" | "number" | "file" | "pic" | "html" | "date" | "url";
}

export interface ExcelValueObject extends PATTR {
  value: string | number | boolean | undefined | null;
  /** 超链接，若链接到某个单元格，则将表名和列名(键名)和值用#拼接，如 Sheet1#id#12345 */
  link?: string;
  /** 其它自定义约束：透传给 exceljs 的 dataValidation */
  validation?: ExcelDataValidation;
}

export interface ExcelRow {
  /** 有行号则修改，无行号则新增 */
  rowNumber?: number;
  [key: string]: string | number | boolean | undefined | null | ExcelValueObject;
}

export interface ExcelOption {
  columns?: {
    [sheetName: string]: {
      header: string;
      key?: string;
      width?: number;
    }[];
  };
}


export const defaultStyle = {
  "font": {
    "bold": true,
    "color": {
      "argb": "FFFFFFFF"
    }
  },
  "border": {
    "left": {
      "style": "thin"
    },
    "right": {
      "style": "thin"
    },
    "top": {
      "style": "thin"
    },
    "bottom": {
      "style": "thin"
    }
  },
  "fill": {
    "type": "pattern",
    "pattern": "solid",
    "fgColor": {
      "argb": "FF0070C0"
    }
  },
  "alignment": {
    "horizontal": "center",
    "vertical": "middle"
  }
}


function generate16CharHexId() {
  const randomBytes = crypto.randomBytes(8);
  const hex = randomBytes.toString('hex');

  return hex.split('').map((char, i) =>
    i % 2 === 0 ? char.toUpperCase() : char.toLowerCase()
  ).join('');
}

/** 工具：保证目录存在 */
function ensureDir(file: string) {
  const dir = path.dirname(path.resolve(file));
  if (!fs.existsSync(dir)) fs.mkdirSync(dir, { recursive: true });
}

/** 工具：将 ExcelValueObject 或原始值写入单元格，处理超链接/校验/下拉等 */
function applyCellValue(ws: Worksheet, keyToColLetter: Map<string, string>, cell: ExcelJS.Cell, v: any) {
    console.log("v",v)
  if (v && typeof v === "object" && "value" in v) {
    const evo = v as ExcelValueObject;
    // 值
    // (cell as any).value = evo.value as any;

    // 超链接
    if (evo.link) {
      // const link = resolveLink(ws.workbook, keyToColLetter, evo.link);
      // if (link) {
      //   (cell as any).value = { text: String(evo.value ?? ""), hyperlink: link } as any;
      // }
      cell.font = {
          ...cell.font, // 保留原有字体设置
          underline: true,
          color: { argb: 'FF0000FF' } // 蓝色，类似 Excel 默认链接颜色
      };

      (cell as any).value = { formula:'=HYPERLINK("#"&ADDRESS(MATCH("XXX01",INDEX(产品!$1:$999999,0,MATCH("文件夹",产品!$1:$1,0)),0),MATCH("文件夹",产品!$1:$1,0),1,1,"产品"),"XXX01")' } as any;


      
    }

    // 校验：优先透传 validation；否则根据 PATTR 自动生成
    if (evo.validation) {
      cell.dataValidation = evo.validation;
    } else {
      const allowBlank = evo.required === true ? false : true;
      if (evo.options && evo.options.length) {
        // 下拉来源：静态列表
        cell.dataValidation = {
          type: "list",
          allowBlank,
          formulae: ['"' + evo.options.map(safeCsv).join(",") + '"'],
          showErrorMessage: true,
          error: "不在允许选项内",
        };
      } else if (typeof evo.min === "number" || typeof evo.max === "number") {
        // 数值范围或文本长度范围
        const isNum = evo.type === "number";
        if (isNum) {
          const min = evo.min ?? Number.MIN_SAFE_INTEGER;
          const max = evo.max ?? Number.MAX_SAFE_INTEGER;
          cell.dataValidation = {
            type: "whole",
            operator: "between",
            allowBlank,
            formulae: [min, max],
            showErrorMessage: true,
            error: `必须为 ${min} 到 ${max} 的数字`,
          };
        } else {
          const min = evo.min ?? 0;
          const max = evo.max ?? 32767;
          cell.dataValidation = {
            type: "textLength",
            operator: "between",
            allowBlank,
            formulae: [min, max],
            showErrorMessage: true,
            error: `文本长度需在 ${min}-${max}`,
          };
        }
      } else if (evo.required) {
        // 仅非空
        cell.dataValidation = {
          type: "custom",
          allowBlank: false,
          formulae: ["LEN(TRIM(INDIRECT(ADDRESS(ROW(),COLUMN()))))>0"],
          showErrorMessage: true,
          error: "必填",
        };
      }
    }
    // 其余 PATTR（unit, delWords, canInput, multiple...）作为注释信息，便于前端或运营识别
    const meta: Record<string, any> = { ...evo };
    delete meta.value;
    delete meta.validation;
    delete meta.link;
    if (Object.keys(meta).length) {
      cell.note = JSON.stringify(meta, null, 0);
    }
  } else {
    // 原始值
    (cell as any).value = v as any;
  }
}

/** 工具：解析链接：
 *  1) http/https/mailto 等直接返回
 *  2) 内部单元格：Sheet#key#value => 转成 'Sheet'!A{row}
 */
function resolveLink(wb: Workbook, keyToColLetter: Map<string, string>, link: string): string | null {
  if (/^[a-z]+:/i.test(link)) return link; // 外链
  const parts = link.split("#");
  if (parts.length !== 3) return null;
  const [sheetName, key, value] = parts;
  const ws = wb.getWorksheet(sheetName);
  if (!ws) return null;
  // 找列字母
  const colLetter = keyToColLetter.get(key);
  if (!colLetter) return null;

  // 遍历查找目标行（首行是表头，从第二行开始）
  for (let r = 2; r <= ws.rowCount; r++) {
    const cell = ws.getCell(`${colLetter}${r}`);
    const cellVal = getCellRawValue(cell);
    if (String(cellVal) === String(value)) {
      return `#'${sheetName}'!${colLetter}${r}`; // 内部引用
    }
  }
  return null;
}

function getCellRawValue(cell: ExcelJS.Cell) {
  const v = (cell as any).value as any;
  if (v && typeof v === "object" && "result" in v) return (v as any).result;
  if (v && typeof v === "object" && "text" in v && "hyperlink" in v) return (v as any).text;
  return v;
}

/** 工具：安全拼接 CSV */
function safeCsv(s: string) {
  const needsQuote = /[",\n]/.test(s);
  const esc = s.replace(/"/g, '""');
  return needsQuote ? `"${esc}"` : esc;
}

/** 获取工作表；若 columns 中存在定义则按定义创建（或补全列） */
// function ensureSheet(wb: Workbook, sheetName: string, def?: ExcelOption["columns"][string]) {
function ensureSheet(wb: Workbook, sheetName: string, def?: NonNullable<ExcelOption["columns"]>[string]) {
  let ws = wb.getWorksheet(sheetName);
  if (!ws) {
    ws = wb.addWorksheet(sheetName);
  }

  if (def) {
    ws.columns = def.map((c) => ({
      header: c.header,
      key: c.key || c.header,
      width: c.width ?? 20
    }));

    // 设置样式
    const headerRow = ws.getRow(1);
    Object.assign(headerRow, defaultStyle);
    headerRow.commit();
  }



  return ws;
}

/** 获取 key -> 列字母 的映射 */
function getKeyToColLetter(ws: Worksheet) {
  const map = new Map<string, string>();
  (ws.columns || []).forEach((col, i) => {
    const key = String(col.key);
    const letter = ws.getColumn(i + 1).letter as string;
    map.set(key, letter);
  });
  return map;
}

/** 将对象行写入到指定行（有行号则覆盖该行，没有则追加到表尾） */
function writeRow(ws: Worksheet, keyToColLetter: Map<string, string>, row: ExcelRow): number {
  let rowNumber = row.rowNumber && row.rowNumber > 0 ? row.rowNumber : ws.rowCount + 1;
  if (rowNumber === 1) rowNumber = 2; // 第 1 行留给表头
  const keys = Array.from(keyToColLetter.keys());
  for (const key of keys) {
    if (!(key in row)) continue;
    const letter = keyToColLetter.get(key);
    const cell = ws.getCell(`${letter}${rowNumber}`);
    applyCellValue(ws, keyToColLetter, cell, (row as any)[key]);
  }
  return rowNumber;
}

/** 读取整表为对象数组（带 rowNumber） */
function readAllRows(ws: Worksheet) {
  const keyToColLetter = getKeyToColLetter(ws);
  const rows: any[] = [];
  for (let r = 2; r <= ws.rowCount; r++) {
    const obj: any = { rowNumber: r };
    for (const [key, letter] of keyToColLetter.entries()) {
      const cell = ws.getCell(`${letter}${r}`);
      const v = getCellRawValue(cell);
      obj[key] = v;
    }
    // 判断空行（所有值均为空）
    const allEmpty = Object.keys(obj).every(k => k === "rowNumber" || obj[k] === undefined || obj[k] === null || obj[k] === "");
    if (!allEmpty) rows.push(obj);
  }
  return rows;
}

/** 删除多行：支持传入数字行号或带 rowNumber 的对象 */
function deleteRowsByRefs(ws: Worksheet, ...rows: (number | ExcelRow)[]) {
  const rowNumbers = rows
    .map(r => typeof r === "number" ? r : (r.rowNumber || 0))
    .filter(n => n >= 2);
  // 倒序删除，避免行号位移
  rowNumbers.sort((a, b) => b - a).forEach(n => {
    if (n <= ws.rowCount) ws.spliceRows(n, 1);
  });
}

export async function excel(file: string, { columns }: ExcelOption = {}): Promise<{
  workbook: Workbook;
  saveRows: (sheetName: string, ...rows: ExcelRow[]) => Promise<ExcelRow[]>;
  //   getRows: (sheetName: string) => any[];
  //   deleteRows: (sheetName: string, ...rows: (number | ExcelRow)[]) => Promise<void>;
}> {
  ensureDir(file);
  const wb = new ExcelJS.Workbook();
  if (fs.existsSync(file)) {
    await wb.xlsx.readFile(file);
  }
  // 初始化/补齐工作表
  if (columns) {
    for (const [sheetName, defs] of Object.entries(columns)) {
      ensureSheet(wb, sheetName, defs);
    }
  }

  async function persist() {
    await wb.xlsx.writeFile(file);
  }

  async function saveRows(sheetName: string, ...rows: ExcelRow[]): Promise<ExcelRow[]> {
    const ws = ensureSheet(wb, sheetName);
    const keyToColLetter = getKeyToColLetter(ws);
    const out: ExcelRow[] = [];
    for (const row of rows) {
      const rowNum = writeRow(ws, keyToColLetter, row);
      out.push({ ...row, rowNumber: rowNum });
    }
    await persist();
    return out;
  }

  function getRows(sheetName: string) {
    const defs = columns?.[sheetName];
    const ws = ensureSheet(wb, sheetName, defs);
    return readAllRows(ws);
  }

  async function deleteRows(sheetName: string, ...rows: (number | ExcelRow)[]): Promise<void> {
    const defs = columns?.[sheetName];
    const ws = ensureSheet(wb, sheetName, defs);
    deleteRowsByRefs(ws, ...rows);
    await persist();
  }

  // 首次创建时写盘，确保空文件存在
  // await persist();

  //   return { workbook: wb, saveRows, getRows, deleteRows };
  return { workbook: wb, saveRows };
}

export type { Workbook } from "exceljs";
